﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Demo
{
    public partial class ADO : Form
    {
        public ADO()
        {
            InitializeComponent();
        }

        private void sqlVersionButton_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();

            sqlConnection.ConnectionString =
                "Data Source=dell-n5110;Initial Catalog=Northwind; Integrated Security=true;";
            //"Data Source=dell-n5110;Initial Catalog=Northwind; User id = abc; Password= def; Integrated Security=false;";

            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand(
                "SELECT * FROM Categories",
                sqlConnection);

            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

            MessageBox.Show(sqlDataReader.FieldCount.ToString());
            if (sqlDataReader.HasRows == false)
                MessageBox.Show("Khong co dong nao");
            else
                while (sqlDataReader.Read())
                {
                    MessageBox.Show(sqlDataReader.GetInt32(0).ToString());
                }

            MessageBox.Show(string.Format("Database : {0}, Version : {1}", sqlConnection.Database, sqlConnection.ServerVersion));
            sqlConnection.Close();
        }

        private void nonQueryButton_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();

            sqlConnection.ConnectionString =
                "Data Source=dell-n5110;Initial Catalog=Northwind; Integrated Security=true;";

            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand(
                "UPDATE Categories SET [Description] = 'TEST' WHERE CategoryID = 1",
                sqlConnection);

            int result = sqlCommand.ExecuteNonQuery();

            if (result > 0)
                sqlConnection.Close();

        }

        private void dataAdapterButton_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();

            sqlConnection.ConnectionString =
                "Data Source=dell-n5110;Initial Catalog=Northwind; Integrated Security=true;";

            sqlConnection.Open();
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(
                "SELECT * FROM Categories", sqlConnection);

            SqlCommand updateCommand = new SqlCommand();

            updateCommand.CommandText =
                "UPDATE Categories SET CategoryName = @CategoryName, Description = @Description WHERE CategoryId = @CategoryId";
            updateCommand.Connection = sqlConnection;
            updateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");
            updateCommand.Parameters.Add("@Description", SqlDbType.NText, 0, "Description");
            SqlParameter sqlParameter = new SqlParameter("@CategoryId", SqlDbType.Int, 4, "CategoryId"); 
            sqlParameter.SourceVersion = DataRowVersion.Original;
            updateCommand.Parameters.Add(sqlParameter);


            sqlDataAdapter.UpdateCommand = updateCommand;
            //sqlDataAdapter.InsertCommand = new SqlCommand("INSERT INTO Categories VALUES(@CategoryId, @CategoryName, @Description)", sqlConnection);
            //sqlDataAdapter.DeleteCommand = new SqlCommand("DELETE FROM Categories WHERE CategoryId = @CategoryId", sqlConnection);

            DataSet dataSet = new DataSet();
            // Lấy dữ liệu từ Database -> DataSet
            sqlDataAdapter.Fill(dataSet, "Categories");

            DataTable dataTable = dataSet.Tables[0];

            dataTable.Rows[0]["Description"] = "Soft drinks Update";
            // Update dữ liệu từ DataSet -> Database
            sqlDataAdapter.Update(dataSet, "Categories");

            sqlConnection.Close();

            dataGridView1.DataMember = "Categories";
            dataGridView1.DataSource = dataSet;
        }

        private void makeDataTableButton_Click(object sender, EventArgs e)
        {
            DataTable dataTable = new DataTable("Categories");

            DataColumn dataColumn = new DataColumn();

            dataColumn.ColumnName = "CategoryID";
            dataColumn.DataType = typeof (int);

            dataTable.Columns.Add(dataColumn);
            dataTable.PrimaryKey = new DataColumn[] { dataColumn };
            
            dataColumn = new DataColumn();

            dataColumn.ColumnName = "CategoryName";
            dataColumn.DataType = typeof (string);
            dataColumn.MaxLength = 15;

            dataTable.Columns.Add(dataColumn);
            dataTable.Columns.Add("Description", typeof(string));

            DataRow newRow = dataTable.NewRow();

            newRow["CategoryID"] = 1;
            newRow["CategoryName"] = "Beverages";
            newRow["Description"] = "Soft drinks, coffees, teas, beers, and ales";

            dataTable.Rows.Add(newRow);

            newRow = dataTable.NewRow();

            newRow["CategoryID"] = 2;
            newRow["CategoryName"] = "Condiments";
            newRow["Description"] = "Sweet and savory sauces, relishes, spreads, and seasonings";

            dataTable.Rows.Add(newRow);
            newRow = dataTable.NewRow();

            newRow["CategoryID"] = 3;
            newRow["CategoryName"] = "Confections";
            newRow["Description"] = "Desserts, candies, and sweet breads";

            dataTable.Rows.Add(newRow);
            dataTable.DefaultView.Sort = "CategoryID DESC,CategoryName ASC";

            DataView dataView = new DataView(dataTable);
            dataView.RowFilter = "CategoryID > 1 AND CategoryId < 3";

            dataGridView1.AutoGenerateColumns = true;
            dataGridView1.DataSource = dataTable.DefaultView;
            dataGridView2.DataSource = dataView;
            //dataGridView1.SelectedRows[0].Cells[0]; //-> CategoryID
            MessageBox.Show(dataGridView1.Rows[0].Cells[0].Value.ToString());
        }
    }
}